MySQL Queries - Full Question This Time :)

The tables are as follows: (use a fixed-width font to view)

master;
+------------------+--------------+------+-----+----------+-------+
| Field            | Type         | Null | Key | Default  | Extra |
+------------------+--------------+------+-----+----------+-------+
| ID               | varchar(15)  |      | MUL |          |       |
| Label            | varchar(50)  | YES  | MUL | NULL     |       |
| LabelNumber      | varchar(50)  | YES  |     | NULL     |       |
| Title            | varchar(100) | YES  | MUL | NULL     |       |
| Media            | text         | YES  |     | NULL     |       |
| TotalPlayingTime | time         |      |     | 00:00:00 |       |
| Date             | date         | YES  |     | NULL     |       |
| Comments         | longtext     | YES  |     | NULL     |       |
+------------------+--------------+------+-----+----------+-------+

artist;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| ID         | varchar(15) |      | MUL |         |       |
| Name       | varchar(50) |      | MUL |         |       |
| ArtistType | varchar(50) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

track;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| ID          | varchar(15) |      | MUL |         |       |
| TrackNumber | varchar(15) |      |     |         |       |
| TrackLength | time        | YES  |     | NULL    |       |
| Title       | varchar(75) |      | MUL |         |       |
+-------------+-------------+------+-----+---------+-------+

And the Indexes for each are as follows:

master;
+--------+------------+-------------+--------------+-------------+-----------+--
-----------+----------+--------+---------+
| Table  | Non_unique | Key_name    | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Comment |
+--------+------------+-------------+--------------+-------------+-----------+--
-----------+----------+--------+---------+
| master |          1 | ID_Index    |            1 | ID          | A
|       20973 |     NULL | NULL   |         |
| master |          1 | Title_Index |            1 | Title       | A
|       20973 |     NULL | NULL   |         |
| master |          1 | Label_Index |            1 | Label       | A
|        3495 |     NULL | NULL   |         |
+--------+------------+-------------+--------------+-------------+-----------+--
-----------+----------+--------+---------+

artist;
+--------+------------+--------------+--------------+-------------+-----------+-
------------+----------+--------+---------+
| Table  | Non_unique | Key_name     | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Comment |
+--------+------------+--------------+--------------+-------------+-----------+-
------------+----------+--------+---------+
| artist |          1 | Artist_Index |            1 | Name        | A
|       23179 |     NULL | NULL   |         |
| artist |          1 | Artist_Index |            2 | ArtistType  | A
|       23179 |     NULL | NULL   |         |
| artist |          1 | ID_Index     |            1 | ID          | A
|       15453 |     NULL | NULL   |         |
+--------+------------+--------------+--------------+-------------+-----------+-
------------+----------+--------+---------+

track;
+-------+------------+-------------+--------------+-------------+-----------+---
----------+----------+--------+---------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation
| Cardinality | Sub_part | Packed | Comment |
+-------+------------+-------------+--------------+-------------+-----------+---
----------+----------+--------+---------+
| track |          1 | ID_Index    |            1 | ID          | A
|       20025 |     NULL | NULL   |         |
| track |          1 | Title_Index |            1 | Title       | A
|      120152 |     NULL | NULL   |         |
+-------+------------+-------------+--------------+-------------+-----------+---
----------+----------+--------+---------+

The number of records in each of the tables is as follows:

master: 20,973
artist: 46,359
track: 240,305

What I was trying to do was get a list of ID's from the master table where
all the keywords were found in either the master, artist or track tables. I
was attempting something like the following statement (:

SELECT master.ID FROM master,artist,track WHERE (master.Title LIKE "%kwd1%"
or artist.Name LIKE "%kwd1%" or track.Title LIKE "%kwd1%") AND
(master.Title LIKE "%kwd2%" or artist.Name LIKE "%kwd2%" or track.Title
LIKE "%kwd2%") AND (master.ID = artist.ID AND master.ID = track.ID);

Depending on the keywords (and the number of them) I'm averaging between 20
and 40 seconds for any results (on a good day). What I want to know is if
this is the right method to go about doing this, if not, where are the
biggest holes, and can the time be improved.

Any thoughts? (Thanks in advance!)

~ Nathan Lyle

Email: natelyle(at)chartermi.net
Phone: (906)485-4806
http://www.nathanlyle.com

"Those that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety." - Ben Franklin

HWG hwg-techniques mailing list archives, maintained by Webmasters @ IWA